package com.decent.ejfadmin.migration.dao;

import com.decent.ejfadmin.migration.entities.MigrationReportVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 豆乐卡迁移外放功能sql管理
 *
 * @author wangyx
 */
@Mapper
public interface MigrationDao {
    /**
     * 收卡统计
     *
     * @param startTime 查询开始时间
     * @param endTime   查询结束时间
     * @return list
     */
    @Select("select u.account,\n" +
            "       i.name,\n" +
            "       o.card_operator_name,\n" +
            "       t.face,\n" +
            "       count(*)                as receiveNumber,\n" +
            "       sum(t.preSellingNumber) as preSellingNumber,\n" +
            "       sum(t.sellingNumber)    as sellingNumber,\n" +
            "       sum(t.successNumber)    as successNumber\n" +
            "from (select IF(c.status = 3, 1, 0) as preSellingNumber,\n" +
            "             if(c.status = 0, 1, 0) as sellingNumber,\n" +
            "             if(c.status = 1, 1, 0) as successNumber,\n" +
            "             c.userId,\n" +
            "             c.productType,\n" +
            "             c.face\n" +
            "      from yuhuang_supply_card c\n" +
            "      where c.startTime >= #{startTime}\n" +
            "        and c.startTime <= #{endTime}) t,\n" +
            "     yuhuang_card_operator o,\n" +
            "     yuhuang_sup_user u,\n" +
            "     yuhuang_sup_info i\n" +
            "where t.userId = u.user_id\n" +
            "  and t.productType = o.card_operator_id\n" +
            "  and u.user_id = i.user_id\n" +
            "group by u.account, i.name, o.card_operator_name, t.face\n" +
            "order by u.account")
    List<MigrationReportVO> report(@Param("startTime") String startTime, @Param("endTime") String endTime);
}
